Hierarchy functions
Hierarchy function is:
· MDX function that is always applied to all elements from single dimension within set on a single axis (rows or columns),
List of functions that are supported in designer:
|
CubePlayer Functions |
MDX Functions used to create CubePlayer
Functions |
|
Best
N |
Equivalent
to TopCount |
|
Best in
Sum |
Equivalent
to TopSum |
|
Best in
Percent |
Equivalent
to TopPercent |
|
Worst
N |
Equivalent
to BottomCount |
|
Worst in
Sum |
Equivalent
to BottomSun |
|
Worst in
Percent |
Equivalent
to BottomPercent |
|
Order |
Equivalent
to Order |
|
Order by
Alphabet |
Combination
of Order function and order conditions |
|
Order by
Hierarchy |
Equivalent
to Hierachize |
|
Filter |
Equivalent
to Filter |
|
Remove
member(s) |
Combination
of Filter (and NOT members …) |
|
First
N |
Equivalent
to Head |
|
Last
N |
Equivalent
to Tail |
|
Without
empties |
Equivalent
to NonEmpty |
|
Show Visual
Totals |
Equivalent
to VisualTotals |
|
Parallel
Time Members |
Implementation of generate and ParallelPeriods |
|
DrillD Down
Level |
Implementations of Generate to select
level |
On each axis, once hierarchy
function is applied each member from selected hierarchy will have red box with
white letters fn
This way you will always be notified about the function
presence.
Hierarchy
functions
Hierarchy functions will always be applied around one dimension/hierarchy.
Example
We have level
We will apply TopCount 100 on entire axis
SELECT
NON
EMPTY
TopCount
(
{
[Customer].[hCountry].[State Province].MEMBERS
}
,100, [Measures].[Store Sales]
)
ON
AXIS(0)
FROM
[Sales]
At this point Axis function and Dimension function are the same since they are applied on only one dimension.
Let us add another dimension/hierarchy Product Family
SELECT
NON
EMPTY
CrossJoin
(
TopCount
(
{
[Customer].[hCountry].[State Province].MEMBERS
},
100,
[Measures].[Store
Sales]
),
{
[Product].[hProduct].[Product Family].MEMBERS
}
)
ON
AXIS(0)
FROM
[Sales]
As you can see TopCount is now inside CrossJoin applied only on dimension Customer.
Let us add another member or level
from dimension Customer. In our case Member
SELECT
NON
EMPTY
CrossJoin
(
TopCount
(
{
[Customer].[hCountry].[State Province].MEMBERS
,
[Customer].[hCountry].[Country].&[USA]
},
100,
[Measures].[Store
Sales]
),
{
[Product].[hProduct].[Product Family].MEMBERS
}
)
ON
AXIS(0)
FROM
[Sales]
Since TopCount is applied to dimension Customer, member from dimension Customer
is placed inside TopCount function, therefore TopCount function will have influence to
that member as well.